/**
 * @Pinar
 */
package at.ac.tuwien.ifs.calendit.basis.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import at.ac.tuwien.ifs.calendit.basis.helper.Constants;
import at.ac.tuwien.ifs.calendit.dao.ITermin;
import at.ac.tuwien.ifs.calendit.domain.Termin;


/**
 * @author Pinar
 *
 *
 */
public class JDBCTerminDAO implements ITermin {
	/***
  	 * Retrieves the logger for this class.
  	 */
	private Logger logger = Logger.getLogger( JDBCTerminDAO.class );
	/***
 	 * SQL Datasource. In order to work with data from a database, we need to
 	 * obtain a connection to the database. A Datasource is part of the JDBC
 	 * specification and can be seen as a generalized connection factory.
	 */
	private DataSource dataSource = null;

	/***
	 	 * Transaction Manager. For encapsulating insert and updates in transaction.
	 	 */
	private PlatformTransactionManager transactionManager = null;
	/***
  	 * SQL Query Strings.
  	 */
		private String sql_getAllTermin ="";
		private String sql_getTermin = "";
		private String sql_insertTermin = "";
		private String sql_getInsertId = "";
		private String sql_updateTermin = "";
		private String sql_deleteTermin = "";
		private String sql_deleteAllTermin ="";
		private String sql_getTerminId="";
		private String sql_getTerminToTerminTyp="";
		private String sql_getTerminToMitarbeiterId="";
		private String sql_getTerminToBezeichnung="";
		private String sql_getIdOfTerminTime="";
		private String sql_getRaumTermin="";
		private String sql_getTerminOfDay="";
	
		
		

	  	/**
		 * @return the sql_getIdOfTerminTime
		 */
		public String getSql_getIdOfTerminTime() {
			return sql_getIdOfTerminTime;
		}

		/**
		 * @param sql_getIdOfTerminTime the sql_getIdOfTerminTime to set
		 */
		public void setSql_getIdOfTerminTime(String sql_getIdOfTerminTime) {
			this.sql_getIdOfTerminTime = sql_getIdOfTerminTime;
		}

		/**
		 * @return the transactionManager
		 */
		public PlatformTransactionManager getTransactionManager() {
			return transactionManager;
		}

		/**
		 * @return the sql_getAllTermin
		 */
		public String getSql_getAllTermin() {
			return sql_getAllTermin;
		}

		/**
		 * @return the sql_getTermin
		 */
		public String getSql_getTermin() {
			return sql_getTermin;
		}

		/**
		 * @return the sql_insertTermin
		 */
		public String getSql_insertTermin() {
			return sql_insertTermin;
		}

		/**
		 * @return the sql_getInsertId
		 */
		public String getSql_getInsertId() {
			return sql_getInsertId;
		}

		/**
		 * @return the sql_updateTermin
		 */
		public String getSql_updateTermin() {
			return sql_updateTermin;
		}

		/**
		 * @return the sql_deleteTermin
		 */
		public String getSql_deleteTermin() {
			return sql_deleteTermin;
		}

		/**
		 * @return the sql_getTerminToTerminTyp
		 */
		public String getSql_getTerminToTerminTyp() {
			return sql_getTerminToTerminTyp;
		}

		/**
		 * @return the sql_getTerminToMitarbeiterId
		 */
		public String getSql_getTerminToMitarbeiterId() {
			return sql_getTerminToMitarbeiterId;
		}

		/**
		 * @return the sql_getTerminToBezeichnung
		 */
		public String getSql_getTerminToBezeichnung() {
			return sql_getTerminToBezeichnung;
		}
		/**
		 * @param sql_getRaumTermin the sql_getRaumTermin to set
		 */
		public void setSql_getRaumTermin(String sql_getRaumTermin) {
			this.sql_getRaumTermin = sql_getRaumTermin;
		}

		/**
		 * @return the sql_getRaumTermin
		 */
		public String getSql_getRaumTermin() {
			return sql_getRaumTermin;
		}

		/***
	  	  	 * Query Objects.
	  	 	 */
		private Query_GetAllTermin query_getAllTermin;
	  	private Query_GetTermin query_getTermin;
	  	private Query_InsertTermin query_insertTermin;
	  	private Query_UpdateTermin query_updateTermin;
	  	private Query_DeleteTermin query_deleteTermin;
	  	private Query_DeleteAllTermin query_deleteAllTermin;
	  	private Query_GetTerminId query_getTerminId;
	  	private	Query_GetTerminToTerminTyp query_getTerminToTermintyp;
	  	private	Query_GetTerminToMitarbeiterId query_getTerminToMitarbeiterId;
	  	private	Query_GetTerminToBezeichnung query_getTerminToBezeichnung;
	  	private Query_GetIdOfTerminTime query_getIdOfTerminTime;
	  	private Query_GetRaumTermin query_getRaumTermin;
	  	private Query_GetTerminOfDay query_getTerminOfDay;




	  	/*** ******************************************************************* */
	  	 	/*** ******************************************************************* */
	  	 	/* INNER CLASSES TO DEFINE SQL STATEMENTS AND MAPPINGS */
	  	 	/*** ******************************************************************* */
	  	 	/*** ******************************************************************* */
	  	 
	  	 	/***
	  	 	 * Defines the base class used for all other queries. This is a private
	  	 	 * inner class, as this is used only in this data access object
	  	 */
	  	
	private class BaseTerminQuery extends MappingSqlQuery{

		
		/***
		  		 * The Base query is extended by all other queries
		  		 * 
		  		 * @param ds SQL Datasource
		  		 * @see #dataSource
		  		 * @param sql  The SQL search String
		  		 */
		public BaseTerminQuery(DataSource ds, String sql) {
		 			super(ds, sql);
  		}

		protected Termin mapRow(ResultSet rs, int rowNumber)
						throws SQLException {
			Termin termin = new Termin();
				termin.setId(rs.getInt("tid"));
				termin.setTermintyp(rs.getInt("termintyp"));
				termin.setMitarbeiterID(rs.getInt("mitarbeiterID"));
				termin.setBezeichnung(rs.getString("bezeichnung"));
				termin.setBeschreibung(rs.getString("beschreibung"));
				termin.setBeginnDatum(rs.getDate("beginndatum"));
				termin.setEndDatum(rs.getDate("enddatum"));
				termin.setBeginnUhrzeit(rs.getTime("beginnuhrzeit"));
				termin.setEndUhrzeit(rs.getTime("enduhrzeit"));
				termin.setWiederholung(rs.getString("wiederholung"));
				termin.setReminder(rs.getBoolean("reminder"));
				termin.setMeetingtermin(rs.getBoolean("meetingtermin"));
				termin.setRaumID(rs.getInt("raumID"));
				
						return termin;
		 		}	
	}
	
	/***
	 	 * Retrieves all termin in the DB ordered by their id number. This
	 	 * is a private inner class, as this is used only in this data access
	 	 * object.
	 	 */
	
	private class Query_GetAllTermin extends BaseTerminQuery{

		public Query_GetAllTermin(DataSource ds) {
			super(ds, sql_getAllTermin);
			compile();
			// TODO Auto-generated constructor stub
		}
	}
	
	/***
	 	 * Retrieves all Termin in the DB ordered by their last name. This is a
	 	 * private inner class, as this is used only in this data access object.
	 	 */

	private class Query_GetTermin extends BaseTerminQuery {

		public Query_GetTermin(DataSource ds) {
			super(ds, sql_getTermin);
			super.declareParameter(new SqlParameter("tid", Types.INTEGER));
			compile();
		}
		
	}
	/***
	 * Adds oneTermin to database. This is a private inner class, as this is
	 * used only in this data access object.
	 * SqlUpdate is a helper class provided by the Spring-Framework
	 */
	 	private class Query_InsertTermin extends SqlUpdate {
	/***
	* Retrieves the ID of the Termin added to database.
	* 
	* @param ds  SQL Datasource
	* @see #dataSource
	*/
	
	public Query_InsertTermin(DataSource ds) {
	 			setDataSource(ds);
	 			setSql(sql_insertTermin);
	 			declareParameter(new SqlParameter("tid", Types.INTEGER));
	 			declareParameter(new SqlParameter("termintyp", Types.INTEGER));
	 			declareParameter(new SqlParameter("mitarbeiterID", Types.INTEGER));
	 			declareParameter(new SqlParameter("bezeichnung", Types.VARCHAR));
	 			declareParameter(new SqlParameter("beschreibung", Types.VARCHAR));
	 			declareParameter(new SqlParameter("beginndatum", Types.DATE));
	 			declareParameter(new SqlParameter("enddatum", Types.DATE));
	 			declareParameter(new SqlParameter("beginnuhrzeit", Types.TIME));
	 			declareParameter(new SqlParameter("enduhrzeit", Types.TIME));
	 			declareParameter(new SqlParameter("wiederholung", Types.CHAR));
	 			declareParameter(new SqlParameter("reminder", Types.BOOLEAN));
	 			declareParameter(new SqlParameter("meetingtermin", Types.BOOLEAN));
	 			declareParameter(new SqlParameter("raumID", Types.INTEGER));
			}
	 	}
	 	
	/***
	* Updates one Termin in the database. This is a private inner class, as
	* this is used only in this data access object.
	* SqlUpdate is a helper class provided by the Spring-Framework
	*/
	private class Query_UpdateTermin extends SqlUpdate {
	/***
	* Create and initialises the SQL-Query for updating a Termin .
	* 
	* @param ds  SQL Datasource
	* @see #dataSource
	*/
	public Query_UpdateTermin (DataSource ds) {
	 	 			setDataSource(ds);
	 	 			setSql(sql_updateTermin );
	 	 			declareParameter(new SqlParameter("termintyp", Types.INTEGER));
	 	 			declareParameter(new SqlParameter("bezeichnung", Types.VARCHAR));
		 			declareParameter(new SqlParameter("beschreibung", Types.VARCHAR));
		 			declareParameter(new SqlParameter("beginnuhrzeit", Types.TIME));
		 			declareParameter(new SqlParameter("enduhrzeit", Types.TIME));
		 			declareParameter(new SqlParameter("beginndatum", Types.DATE));
		 			declareParameter(new SqlParameter("enddatum", Types.DATE));
		 			declareParameter(new SqlParameter("wiederholung", Types.CHAR));
		 			declareParameter(new SqlParameter("reminder", Types.VARCHAR));
		 			declareParameter(new SqlParameter("meetingtermin", Types.BOOLEAN));
		 			declareParameter(new SqlParameter("raumID", Types.INTEGER));
		 			declareParameter(new SqlParameter("tid", Types.INTEGER));
		 			declareParameter(new SqlParameter("mitarbeiterID", Types.INTEGER));
	 	 		}
	 	 	}
	private class Query_DeleteTermin extends SqlUpdate {
	/***
	 * Creates and initializes the SQL-Query for deleting a Termin.
	 * 
	 * @param ds  SQL Datasource
	 * @see #dataSource
	 */
	 public Query_DeleteTermin(DataSource ds) {
	 	 		 			setDataSource(ds);
	 	 		 			setSql(sql_deleteTermin);
	 	 		 			declareParameter(new SqlParameter("id", Types.INTEGER));
	 	 		 		}
	 	 		 	}
	 private class Query_DeleteAllTermin extends SqlUpdate {
	 /***
	 * Creates and initializes the SQL-Query for deleting a Termin.
	 * 
	 * @param ds  SQL Datasource
	 * @see #dataSource
	 */
	 public Query_DeleteAllTermin(DataSource datasource) {
	 		super(datasource, sql_deleteAllTermin);
	 		}
	  	}

 	/***
 	 * Retrieves the ID of the Termin added to database. This is a private
 	 * inner class, as this is used only in this data access object.
 	 */
 	private class Query_GetTerminId extends MappingSqlQuery{
	/***
	 * Retrieves the ID of the Termin added to database.
	 * 
	 * @param ds
	 *            SQL Datasource
	 * @see #dataSource
	 */
 	 public Query_GetTerminId(DataSource ds) {
 	 	 			super(ds, sql_getInsertId);
 	 	 			compile();
 	 	 		}
 	 	 
 	  		protected Object mapRow(ResultSet rs, int rowNumber)
 	  				throws SQLException {
 	 	 			// "@p0" is the row name hsqldb creates for the one value returning
 	 	 			// the ID
 	 	 			Integer id = new Integer(rs.getInt("@p0"));
 	 	 			return id;
 	 	 		}
 	 	 	}
	/**
	* retrieves only those Kunde entries which contain the vorname-search String	 *
	*/
	private class Query_GetTerminToTerminTyp extends BaseTerminQuery {
	/**
	* 
	* @param dataSource SQL Datasource
	*/
			public Query_GetTerminToTerminTyp(DataSource dataSource) {
	 				super(dataSource, sql_getTerminToTerminTyp);
	 				super.declareParameter(new SqlParameter("termintyp", Types.INTEGER));
	 				compile();
	 			}
	 		}
	private class Query_GetTerminToMitarbeiterId extends BaseTerminQuery {
	/**
	 * 
	 * @param dataSource SQL Datasource
	 */
	 			public Query_GetTerminToMitarbeiterId(DataSource dataSource) {
	 				super(dataSource, sql_getTerminToMitarbeiterId);
	 				super.declareParameter(new SqlParameter("mitarbeiterid", Types.INTEGER));
	 				compile();
	 			}
	 		}
	private class Query_GetRaumTermin extends BaseTerminQuery {
		/**
		 * 
		 * @param dataSource SQL Datasource
		 */
		 			public Query_GetRaumTermin(DataSource dataSource) {
		 				super(dataSource, sql_getRaumTermin);
		 				super.declareParameter(new SqlParameter("raumID", Types.INTEGER));
		 				super.declareParameter(new SqlParameter("beginnDatum", Types.INTEGER));
		 				super.declareParameter(new SqlParameter("beginnuhrzeit", Types.TIME));
						super.declareParameter(new SqlParameter("enduhrzeit", Types.TIME));
		 				compile();
		 			}
		 		}
	
	private class Query_GetTerminOfDay extends BaseTerminQuery {
		/**
		 * 
		 * @param dataSource SQL Datasource
		 */
		 			public Query_GetTerminOfDay(DataSource dataSource) {
		 				super(dataSource, sql_getTerminOfDay);
		 				super.declareParameter(new SqlParameter("beginnDatum", Types.INTEGER));
		 				super.declareParameter(new SqlParameter("mitarbeiterid", Types.INTEGER));
		 				compile();
		 			}
		 		}
	private class Query_GetTerminToBezeichnung extends BaseTerminQuery {
	/**
	* 
	* @param dataSource SQL Datasource
	*/
	 			public Query_GetTerminToBezeichnung(DataSource dataSource) {
	 				super(dataSource, sql_getTerminToBezeichnung);
	 				super.declareParameter(new SqlParameter("bezeichnung", Types.INTEGER));
	 				compile();
	 			}
	 		}
	 		
	/***
	 * Retrieves all Termin in the DB ordered by their last name. This is a
	 * private inner class, as this is used only in this data access object.
	 */

	private class Query_GetIdOfTerminTime extends BaseTerminQuery {

			public Query_GetIdOfTerminTime(DataSource ds) {
				super(ds, sql_getIdOfTerminTime);
				super.declareParameter(new SqlParameter("beginndatum", Types.DATE));
				super.declareParameter(new SqlParameter("beginnuhrzeit", Types.TIME));
				super.declareParameter(new SqlParameter("enduhrzeit", Types.TIME));
				super.declareParameter(new SqlParameter("mitarbeiterID", Types.INTEGER));
				compile();
			}
			
			
	}		
	 	
	/**
	* Retrieves only those Kunde entries which contain the nachname-search String
	*
	*/
	 public JDBCTerminDAO() {
	 	 	 	super();
	 	 }
	 	 
	/***
	* The Initialise Method must be called after all bean values are set,
	* particularly the datasource and the transaction manager. This is actually
	* performed by the Spring Framework, which sets first of all, all Java Bean
	* Properties and eventually calls this init method (see bean definition in
	* beans.xml configuration file)
	*/
	public void init() {
	 	 		logger.info("Initialise TerminDAO");
	 	 		query_getTermin = new Query_GetTermin(dataSource);
	 	 		query_insertTermin = new Query_InsertTermin(dataSource);
	 			query_updateTermin = new Query_UpdateTermin(dataSource);
	 	 		query_deleteTermin = new Query_DeleteTermin(dataSource);
	  	 		query_getAllTermin = new Query_GetAllTermin(dataSource);
	  	 		query_getTerminId=new Query_GetTerminId(dataSource);
	  	 		query_getTerminToTermintyp=new Query_GetTerminToTerminTyp (dataSource);
	  	 		query_getTerminToMitarbeiterId=new Query_GetTerminToMitarbeiterId(dataSource);
	  	 		query_getTerminToBezeichnung=new Query_GetTerminToBezeichnung(dataSource);
	  	 		query_getIdOfTerminTime= new Query_GetIdOfTerminTime(dataSource);
	  	 		query_getRaumTermin = new Query_GetRaumTermin(dataSource);
	  	 		query_getTerminOfDay= new Query_GetTerminOfDay(dataSource);

	 		}
	 		

	/***
	* The Destroy Method is called by the Spring Framework to end the lifecycle
	* of this bean, but <b>only</b> when the bean is created as singleton.
	* Check the bean definition in beans.xml configuration file for details.
	 */
	public void destroy() {
	 		 		logger.info("Destroy TerminDAO");
	 		 	}
	 		 	/** ******************************************************************* */
	 			/** ******************************************************************* */
	 			/*
	 			 * BEAN SETTERS FOR DEPENDENCY INJECTION
	 			 * 
	 			 * Dependency Injection is a design pattern in which the responsibility for
	 			 * object creation and object linking is removed from the objects themselves
	 			 * and transferred to a factory object. It is a way to achieve loose
	 			 * coupling between objects and results in highly testable objects
	 			 * (controlled unit tests).
	 			 * 
	 			 * Factory Object: (Design Pattern) is an object for creating other objects.
	 			 * Typically a factory has a method for every kind of object it is capable
	 			 * of creating. these methods optionally accept parameters defining how the
	 			 * object is created and then return the created object.
	 			 */
				/** ******************************************************************* */
	 		 	/** ******************************************************************* */
			 	 
/***
 * Sets the SQL String to get all Termins.
 * 
 * @param sql_getAllStudents
 *            SQL Statement as String
 */
	 	public void setSql_getAllTermin(String sql_getAllTermin) {
	 		this.sql_getAllTermin = sql_getAllTermin;
	 	}
/***
 * Sets the SQL String to get one Termin with one SQL parameter.
 * 
 * @param sql_getStudent
 *            SQL Statement as String
 */
	 	public void setSql_getTermin(String sql_getTermin) {
	 		 this.sql_getTermin= sql_getTermin;
	 	}

/***
* Sets the SQL String to insert one Termin into the database.
* 
* @param sql_insertStudent
*            SQL Statement as String
*/
	 	public void setSql_insertTermin(String sql_insertTermin) {
	 	 	this.sql_insertTermin = sql_insertTermin;
	 	}
	 	/**
		* Sets the SQL String to retrieve the ID of the last executed SQL
		* Statement.
		* 
		* @param sql_getInsertId
		*            SQL Statement as String
		*/
		public void setSql_getInsertId(String sql_getInsertId) {
			this.sql_getInsertId = sql_getInsertId;
		}

 	/***
 	 * Sets the SQL String to update a Termin.
	 * 
	 * @param sql_updateStudent
	 *            SQL Statement as String
	 */
	 	public void setSql_updateTermin(String sql_updateTermin) {
	 		this.sql_updateTermin = sql_updateTermin;
	 	}
	 	
	 /***
	  * Sets the SQL String to delete a Termin.
	  * 
	  * @param sql_deleteStudent
	  *            SQL Statement as String
	  */
	 	public void setSql_deleteTermin(String sql_deleteTermin) {
	 		this.sql_deleteTermin = sql_deleteTermin;
	 	 }
	 	
	 	public void setSql_deleteAllTermin(String sql_deleteTermin) {
	 		this.sql_deleteAllTermin = sql_deleteTermin;
	 	 }
	 	
	 /***
	  * Sets the Datasource to connect to database.
	  * 
	  * @param dataSource
	  *            SQL Datasource
	  * @see #dataSource
	  */
	 public void setDataSource(DataSource dataSource) {
	 	 	this.dataSource = dataSource;
	 	}
	 /***
	 * Sets the transaction manager.
	 * 
	 * @param transactionManager
	 *            central interface in Spring's transaction infrastructure
	 * @see #transactionManager
	 */
	 	public void setTransactionManager(PlatformTransactionManager transactionManager) {
	 		this.transactionManager = transactionManager;
	 	}
	 	
		public String getSql_getTerminId() {
			return this.sql_getTerminId;
		}
		public void setSql_getTerminId(String sqlGetTerminId) {
			this.sql_getTerminId = sqlGetTerminId;
		}

		public String getTerminToTerminTyp() {
			return this.sql_getTerminToTerminTyp;
		}

		public void setSql_getTerminToTerminTyp(String sqlGetTerminToTerminTyp) {
			this.sql_getTerminToTerminTyp = sqlGetTerminToTerminTyp;
		}

		public String getTerminToBezeichnung () {
			return this.sql_getTerminToBezeichnung;
		}

		public void setSql_getTerminToBezeichnung(String sqlGetTerminToBezeichnung) {
			this.sql_getTerminToBezeichnung = sqlGetTerminToBezeichnung;
		}

		public String getTerminToMitarbeiterId () {
			return this.sql_getTerminToMitarbeiterId ;
		}

		public void setSql_getTerminToMitarbeiterId(String sqlGetTerminToMitarbeiterId) {
			this.sql_getTerminToMitarbeiterId = sqlGetTerminToMitarbeiterId;
		}
		
		
	
	 	/**
		 * @return the logger
		 */
		public Logger getLogger() {
			return logger;
		}

		/**
		 * @param logger the logger to set
		 */
		public void setLogger(Logger logger) {
			this.logger = logger;
		}

		/**
		 * @return the sql_getTerminOfDay
		 */
		public String getSql_getTerminOfDay() {
			return sql_getTerminOfDay;
		}

		/**
		 * @param sql_getTerminOfDay the sql_getTerminOfDay to set
		 */
		public void setSql_getTerminOfDay(String sql_getTerminOfDay) {
			this.sql_getTerminOfDay = sql_getTerminOfDay;
		}

		/**
		 * @return the query_getAllTermin
		 */
		public Query_GetAllTermin getQuery_getAllTermin() {
			return query_getAllTermin;
		}

		/**
		 * @param query_getAllTermin the query_getAllTermin to set
		 */
		public void setQuery_getAllTermin(Query_GetAllTermin query_getAllTermin) {
			this.query_getAllTermin = query_getAllTermin;
		}

		/**
		 * @return the query_getTermin
		 */
		public Query_GetTermin getQuery_getTermin() {
			return query_getTermin;
		}

		/**
		 * @param query_getTermin the query_getTermin to set
		 */
		public void setQuery_getTermin(Query_GetTermin query_getTermin) {
			this.query_getTermin = query_getTermin;
		}

		/**
		 * @return the query_insertTermin
		 */
		public Query_InsertTermin getQuery_insertTermin() {
			return query_insertTermin;
		}

		/**
		 * @param query_insertTermin the query_insertTermin to set
		 */
		public void setQuery_insertTermin(Query_InsertTermin query_insertTermin) {
			this.query_insertTermin = query_insertTermin;
		}

		/**
		 * @return the query_updateTermin
		 */
		public Query_UpdateTermin getQuery_updateTermin() {
			return query_updateTermin;
		}

		/**
		 * @param query_updateTermin the query_updateTermin to set
		 */
		public void setQuery_updateTermin(Query_UpdateTermin query_updateTermin) {
			this.query_updateTermin = query_updateTermin;
		}

		/**
		 * @return the query_deleteTermin
		 */
		public Query_DeleteTermin getQuery_deleteTermin() {
			return query_deleteTermin;
		}

		/**
		 * @param query_deleteTermin the query_deleteTermin to set
		 */
		public void setQuery_deleteTermin(Query_DeleteTermin query_deleteTermin) {
			this.query_deleteTermin = query_deleteTermin;
		}

		/**
		 * @return the query_deleteAllTermin
		 */
		public Query_DeleteAllTermin getQuery_deleteAllTermin() {
			return query_deleteAllTermin;
		}

		/**
		 * @param query_deleteAllTermin the query_deleteAllTermin to set
		 */
		public void setQuery_deleteAllTermin(Query_DeleteAllTermin query_deleteAllTermin) {
			this.query_deleteAllTermin = query_deleteAllTermin;
		}

		/**
		 * @return the query_getTerminId
		 */
		public Query_GetTerminId getQuery_getTerminId() {
			return query_getTerminId;
		}

		/**
		 * @param query_getTerminId the query_getTerminId to set
		 */
		public void setQuery_getTerminId(Query_GetTerminId query_getTerminId) {
			this.query_getTerminId = query_getTerminId;
		}

		/**
		 * @return the query_getTerminToTermintyp
		 */
		public Query_GetTerminToTerminTyp getQuery_getTerminToTermintyp() {
			return query_getTerminToTermintyp;
		}

		/**
		 * @param query_getTerminToTermintyp the query_getTerminToTermintyp to set
		 */
		public void setQuery_getTerminToTermintyp(
				Query_GetTerminToTerminTyp query_getTerminToTermintyp) {
			this.query_getTerminToTermintyp = query_getTerminToTermintyp;
		}

		/**
		 * @return the query_getTerminToMitarbeiterId
		 */
		public Query_GetTerminToMitarbeiterId getQuery_getTerminToMitarbeiterId() {
			return query_getTerminToMitarbeiterId;
		}

		/**
		 * @param query_getTerminToMitarbeiterId the query_getTerminToMitarbeiterId to set
		 */
		public void setQuery_getTerminToMitarbeiterId(
				Query_GetTerminToMitarbeiterId query_getTerminToMitarbeiterId) {
			this.query_getTerminToMitarbeiterId = query_getTerminToMitarbeiterId;
		}

		/**
		 * @return the query_getTerminToBezeichnung
		 */
		public Query_GetTerminToBezeichnung getQuery_getTerminToBezeichnung() {
			return query_getTerminToBezeichnung;
		}

		/**
		 * @param query_getTerminToBezeichnung the query_getTerminToBezeichnung to set
		 */
		public void setQuery_getTerminToBezeichnung(
				Query_GetTerminToBezeichnung query_getTerminToBezeichnung) {
			this.query_getTerminToBezeichnung = query_getTerminToBezeichnung;
		}

		/**
		 * @return the query_getIdOfTerminTime
		 */
		public Query_GetIdOfTerminTime getQuery_getIdOfTerminTime() {
			return query_getIdOfTerminTime;
		}

		/**
		 * @param query_getIdOfTerminTime the query_getIdOfTerminTime to set
		 */
		public void setQuery_getIdOfTerminTime(
				Query_GetIdOfTerminTime query_getIdOfTerminTime) {
			this.query_getIdOfTerminTime = query_getIdOfTerminTime;
		}

		/**
		 * @return the query_getRaumTermin
		 */
		public Query_GetRaumTermin getQuery_getRaumTermin() {
			return query_getRaumTermin;
		}

		/**
		 * @param query_getRaumTermin the query_getRaumTermin to set
		 */
		public void setQuery_getRaumTermin(Query_GetRaumTermin query_getRaumTermin) {
			this.query_getRaumTermin = query_getRaumTermin;
		}

		/**
		 * @return the query_getTerminOfDay
		 */
		public Query_GetTerminOfDay getQuery_getTerminOfDay() {
			return query_getTerminOfDay;
		}

		/**
		 * @param query_getTerminOfDay the query_getTerminOfDay to set
		 */
		public void setQuery_getTerminOfDay(Query_GetTerminOfDay query_getTerminOfDay) {
			this.query_getTerminOfDay = query_getTerminOfDay;
		}

		/**
		 * @return the dataSource
		 */
		public DataSource getDataSource() {
			return dataSource;
		}

		/**
		 * @return the sql_deleteAllTermin
		 */
		public String getSql_deleteAllTermin() {
			return sql_deleteAllTermin;
		}

			/*** ******************************************************************* */
	 	/*** ******************************************************************* */
	 	/*
	 	 * DAO METHODS
	  	 * 
	  	 * A Data Access Object (DAO) is a component which provides a common
	  	 * interface between the application and one or more data storage devices,
	  	 * such as a database or a file. The advantage of using data access objects
	  	 * is that any business object (which contains application or operation
	  	 * specific details) does not require direct knowledge of the final
	  	 * destination for the information it manipulates. As a result, _if_ it is
	  	 * necessary to change where or how the data is stored, these modifications
	  	 * can be made without needing to change the main application.
	  	 */
	 		/*** ******************************************************************* */
	 		/*** ******************************************************************* */




	public Termin getById(int id) {
		logger.info("Get Termin ID = " + id);
		List termins = query_getTermin.execute(id);
		if (termins.size() == 1) {
			Termin t = (Termin) termins.get(0);
			logger.debug("Returning Termin \"" + t.getId() + " "+ t.getTermintyp()+ " "
					+ t.getMitarbeiterID() + "\"");
			return t;
		} else {
			logger.debug("No Termin data");
			return null;
		}
	}
	/**
	 * Inserts one Termin into the database. This method uses a transaction
	 * manager for performing two queries in one transaction:
	 * <ol>
	 * <li> the insert statement is performed adding the dataset to the database
	 * (first query)</li>
	 * <li> the database then automatically generates a unique ID for this
	 * dataset, </li>
	 * <li> the second query asks the database <i>which</i> id was used for
	 * this particular dataset</li>
	 * <li> this ID then is set in the Termin bean and returned by the function</li>
	 * </ol>
	 * 
	 * @param Termin
	 *            Termin object holding data of one Termin
	 * @return unique id generated by database assigned to the newly created
	 *         Termin
	 */

	public Termin add(Termin termin) {
		logger.debug("Saving Termin \"" + termin.getId() + " "+ termin.getTermintyp()+ " "
				+ termin.getMitarbeiterID() + "\"");
		logger.debug("Initialise SQL Parameters");
		final Object[] param = new Object[] { null,termin.getTermintyp(),
				 termin.getMitarbeiterID(),termin.getBeschreibung(),termin.getBezeichnung(),
					termin.getBeginnDatum(), termin.getEndDatum(), termin.getBeginnUhrzeit(), 
					termin.getEndUhrzeit(), termin.getWiederholung(),termin.getReminder(),
					termin.getMeetingtermin(),termin.getRaumID()};
		logger.debug("Initialise Transaction Manager");
		TransactionTemplate tt = new TransactionTemplate(transactionManager);
		Object result = tt.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus status) {
				// The transaction is run from here
				logger.debug("Start Transaction");
				query_insertTermin.update(param);

				/*
				 * activate the following error line to create an Error which
				 * terminates this method. One will see, that the complete
				 * transaction is rolled back, hence the insert statement above
				 * is not executed, alternatively the second rollback statement
				 * can be activated with the same result which in that case is a
				 * manual rollback of the transaction
				 */

			
				List results = query_getTerminId.execute();
				Integer id = (Integer) results.get(0);
				logger.debug("End Transaction");
				return id;
				
				/*
				 * 
				 * and the transaction ends here! if no error occurs the
				 * transaction is committed by Spring otherwise it is rolled
				 * back
				 */
			}
		});
		Integer id = (Integer) result;
		termin.setId(id);
		logger.info("Return ID from inserted dataset = " + id);
		return (id!=0) ? termin : null;
		// TODO Auto-generated method stub
	}

	
	public Termin update(Termin termin) {
		return update(termin, termin.getId());
	}
	
	public Termin update(Termin termin, long id) {
		logger.info("Update Termin, ID = " + termin.getId() + " new ID = "
				+ id);
		logger.debug("Initialise SQL Parameters");
		final Object[] param = new Object[] {termin.getTermintyp(),termin.getBezeichnung(), termin.getBeschreibung(),
				termin.getBeginnUhrzeit(),termin.getEndUhrzeit(), termin.getBeginnDatum(), termin.getEndDatum(),  termin.getWiederholung(),termin.getReminder(),
				termin.getMeetingtermin(),termin.getRaumID(),termin.getId(),Constants.getUserId()};

		logger.debug("Execute Update");
		if (query_updateTermin.update(param) == 1) {
			logger.debug("Update Successfull");
			return termin;
		}
		logger.error("Update for Termin ID = " + id + " failed.");
		return null;
	}
	/**
	 * Retrieves all students from the database. <br>
	 * <b>Warning:</b> this type of DAO method would not be used in a real-
	 * world application because there may be thousands of students in the
	 * database and this method would retrieve them all. <br>
	 * This is usually not needed: it will generate a huge load on the database
	 * and also require enormous amounts of memory. Morover, there is hardly an
	 * application conceivable that needs more than a few dozen datasets at any
	 * one time.
	 */
	@SuppressWarnings("unchecked")
	public List<Termin> getAll(){
		logger.info("Get all Termin");
		List<Termin> termine= null;
		termine = query_getAllTermin.execute();

		logger.debug("Termin List contains " + termine.size() + " termine");
		return termine;
	}
	
	public boolean delete(int id) {
		logger.info("Delete Termin ID = " + id);
		logger.debug("Initialize SQL Parameters");
		final Object[] param = new Object[] { id };
		logger.debug("Executing SQL");
		if (query_deleteTermin.update(param) == 1) {
			logger.debug("Deleting successfull");
			return true;
		}
		logger.error("Deleting Termin ID = " + id + " failed");
		return false;
	}

	
	public boolean deleteAll() {
		logger.info("Delete All Termin = ");

		if (!(query_deleteAllTermin.update() == 1)) {
			logger.debug("Deleting all Termin successfull");
			return true;
		}
		logger.debug("Deleting All Termin failed");
		return false;
		
	}
	public List<Termin> getTerminToId(String id) {
		logger.info("Getting Termin to ID: " + id);
		List<Termin> result = query_getTerminId.execute(id);
		
		return result;
	}

	
	public List<Termin> getTerminToTermintyp(String typ) {
		logger.info("Getting Termin to termintyp: " + typ);
		List<Termin> result = query_getTerminToTermintyp.execute(typ);
		
		return result;
	}


	public List<Termin> getTerminToMitarbeiterId(String mit) {
		logger.info("Getting Termin to MitarbeiterId: " + mit);
		List<Termin> result = query_getTerminToTermintyp.execute(mit);
		
		return result;
	}

	public List<Termin> getTerminToBezeichnung(String bez) {
		logger.info("Getting Termin to Bezeichnung: " + bez);
		List<Termin> result = query_getTerminToBezeichnung.execute(bez);
		
		return result;
	}
	public Termin getRaumTermin(Termin t) {
		
		logger.info("Getting Termin from Raum: " + t.getRaumID());

		final Object[] param = new Object[] { t.getRaumID(),t.getBeginnDatum(), t.getBeginnUhrzeit(), 
				t.getEndUhrzeit()};
//		final Object[] param = new Object[] {  t.getBeginnUhrzeit(), 
//				t.getEndUhrzeit(), t.getMitarbeiterID()};
		List<Termin> termins = query_getRaumTermin.execute(param);
		if (termins.size() != 0) {
			Termin t1 = (Termin) termins.get(0);
			logger.debug("Returning Termin \"" + t1.getId() + " "+ t1.getTermintyp()+ " "
					+ t1.getMitarbeiterID() + "\"");
//			System.out.println("returniere termin");
			return t1;
		} else {
//			System.out.println("keine Daten");
			logger.debug("No Termin data");
			return null;
		}
	}
	
	public Termin getIdToTerminTime(Termin t) {
		logger.info("Getting Termin to TerminTime: " + t.getMitarbeiterID());
		final Object[] param = new Object[] { t.getBeginnDatum(), t.getBeginnUhrzeit(), 
				t.getEndUhrzeit(), t.getMitarbeiterID()};
//		final Object[] param = new Object[] {  t.getBeginnUhrzeit(), 
//				t.getEndUhrzeit(), t.getMitarbeiterID()};
		List<Termin> termins = query_getIdOfTerminTime.execute(param);
		if (termins.size() != 0) {
			Termin t1 = (Termin) termins.get(0);
			logger.debug("Returning Termin \"" + t1.getId() + " "+ t1.getTermintyp()+ " "
					+ t1.getMitarbeiterID() + "\"");
//			System.out.println("returniere termin");
			return t1;
		} else {
//			System.out.println("keine Daten");
			logger.debug("No Termin data");
			return null;
		}
	}

	public List<Termin> getTermin(String order) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Termin> getAll(Termin t) {
		// TODO Auto-generated method stub
		//Muss ich(Pinar)noch implentieren 
		return null;
	}

	public List<Termin> getTerminToTermintyp(int typ) {
		// TODO Auto-generated method stub
		return null;
	}

public List<Termin> getTerminToDay(Termin t) {
		
		logger.info("Getting Termin for Day " + t.getBeginnDatum());
		
		final Object[] param = new Object[] { t.getBeginnDatum(),t.getMitarbeiterID()};
		List<Termin> result = query_getTerminOfDay.execute(param);
		
		return result;
	}

}


